package com.hc.android.research.persistence;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.hc.android.research.dom.Item;

public class ResearchDAO
{

	
	
	public ResearchDAO()
	{
		
	}
	
	public void addItem(Context context, String itemName, String itemURL) throws Exception
	{
		ResearchDB db = new ResearchDB(context);
		SQLiteDatabase sqlDb = null;
		Cursor c = null;
		
		try 
		{
			sqlDb = db.getReadableDatabase();
			
			ContentValues values = new ContentValues();
			values.put("itemName", itemName);
			values.put("itemURL", itemURL);
			
			sqlDb.insert(ResearchDB.DATABASE_ITEM_TABLE_NAME, null, values);
			
			c = sqlDb.rawQuery("SELECT itemID FROM " + ResearchDB.DATABASE_ITEM_TABLE_NAME + " WHERE itemName = ? AND itemURL = ?;", new String[]{itemName, itemURL});
			if(c != null && c.getCount() > 0)
			{
				c.moveToFirst();
				int itemID = c.getInt(c.getColumnIndex("itemID"));
				ContentValues histValues = new ContentValues();
				histValues.put("itemID", itemID);
				histValues.put("itemPrice", 0);
				histValues.put("priceDate", 0);
				sqlDb.insert(ResearchDB.DATABASE_ITEM_HISTORY_TABLE_NAME, null, histValues);
			}
			else 
			{
				throw new Exception("Unable to insert");
			}
		}
		finally
		{
			if(c != null)
			{
				c.close();
			}
			
			if(sqlDb != null)
			{
				sqlDb.close();
			}
		}		
	}
	
	public void updateItem(Context context, int itemID, String itemName, String itemPrice, String itemURL)
	{
		ResearchDB db = new ResearchDB(context);
		SQLiteDatabase sqlDb = null;
		Cursor c = null;
		
		try 
		{
			sqlDb = db.getReadableDatabase();
			
			ContentValues values = new ContentValues();
			values.put("itemName", itemName);
			values.put("itemURL", itemURL);
			
			sqlDb.update(ResearchDB.DATABASE_ITEM_TABLE_NAME, values, "itemID=?", new String[]{""+itemID});
			
			ContentValues histValues = new ContentValues();
			histValues.put("itemID", itemID);
			histValues.put("itemPrice", itemPrice);
			histValues.put("priceDate", new Timestamp(new Date().getTime()).toString());
			sqlDb.insert(ResearchDB.DATABASE_ITEM_HISTORY_TABLE_NAME, null, histValues);
			//c = sqlDb.rawQuery("INSERT INTO " + ResearchDB.DATABASE_ITEM_HISTORY_TABLE_NAME + " (itemID, itemPrice, priceDate) VALUES (?, ?, DATETIME('now'))", new String[]{""+itemID, itemPrice});

			debugItemHistory(context);
		}
		finally
		{
			if(c != null)
			{
				c.close();
			}
			
			if(sqlDb != null)
			{
				sqlDb.close();
			}
		}
	}
	
	public void deleteItem(Context context, int itemID)
	{
		
	}
	
	public List<Item> getItems(Context context)
	{
		List<Item> items = new ArrayList<Item>();
		
		ResearchDB db = new ResearchDB(context);
		SQLiteDatabase sqlDb = null;
		Cursor c = null;
		
		try 
		{
			sqlDb = db.getReadableDatabase();
			c = sqlDb.rawQuery("SELECT i.itemID, i.itemName, i.itemURL, ih.itemPrice, ih.priceDate FROM " + ResearchDB.DATABASE_ITEM_TABLE_NAME + " i, " + ResearchDB.DATABASE_ITEM_HISTORY_TABLE_NAME + " ih " + 
									  "Where i.itemID = ih.itemID ORDER BY i.itemID", new String[]{});
			
			if(c != null && c.getCount() > 0)
			{
				c.moveToFirst();
				int prevItemID = -1;
				Item curItem = null; 
				
				do
				{
					int itemID = c.getInt(c.getColumnIndex("itemID"));
					String itemName = nullTrim(c.getString(c.getColumnIndex("itemName")));
					String itemPrice = nullTrim(c.getString(c.getColumnIndex("itemPrice")));
					String itemURL = nullTrim(c.getString(c.getColumnIndex("itemURL")));
					String priceDate = nullTrim(c.getString(c.getColumnIndex("priceDate")));
					
					if(prevItemID != itemID)
					{
						prevItemID = itemID;
						curItem = new Item(itemID, itemName, itemURL);
						items.add(curItem);
					}
					
					try
					{
						curItem.addPrice(itemPrice, priceDate);
					}
					catch(Exception e)
					{
						;//Do nothing, if it can't parse the date then the date isn't valid.
					}
					
				} while(c.moveToNext());

				debugItemHistory(context);
			}
		}
		catch(Exception e) 
		{
			e.printStackTrace();
		}
		finally
		{
			if(c != null)
			{
				c.close();
			}
			
			if(sqlDb != null)
			{
				sqlDb.close();
			}
		}
		
		return items;
	}
	
	private void debugItemHistory(Context context)
	{
		ResearchDB db = new ResearchDB(context);
		SQLiteDatabase sqlDb = null;
		Cursor c = null;
		
		try 
		{
			sqlDb = db.getReadableDatabase();
			c = sqlDb.rawQuery("SELECT ih.itemID, ih.itemPrice, ih.priceDate FROM " + ResearchDB.DATABASE_ITEM_HISTORY_TABLE_NAME + " ih ORDER BY ih.itemID", new String[]{});
			
			if(c != null && c.getCount() > 0)
			{
				c.moveToFirst();
				
				do
				{
					int itemID = c.getInt(c.getColumnIndex("itemID"));
					String itemPrice = nullTrim(c.getString(c.getColumnIndex("itemPrice")));
					String priceDate = nullTrim(c.getString(c.getColumnIndex("priceDate")));
					
					System.out.println("ItemID: " + itemID + " itemPrice: " + itemPrice + " priceDate: " + priceDate);
				} while(c.moveToNext());

			}
		}
		catch(Exception e) 
		{
			e.printStackTrace();
		}
		finally
		{
			if(c != null)
			{
				c.close();
			}
			
			if(sqlDb != null)
			{
				sqlDb.close();
			}
		}
	}
	
	private String nullTrim(String toTrim)
	{
		if(toTrim == null)
		{
			return "";
		}
		else
		{
			return toTrim.trim();
		}
	}
	
	public void updateSetting(Context context, String settingName, String settingValue) throws Exception
	{
		ResearchDB db = new ResearchDB(context);
		SQLiteDatabase sqlDb = null;
		Cursor c = null;
		
		try 
		{
			sqlDb = db.getReadableDatabase();
			c = sqlDb.rawQuery("SELECT * FROM " + ResearchDB.DATABASE_SETTINGS_TABLE_NAME + " s Where s.settingName = ?", new String[]{settingName});
			
			ContentValues values = new ContentValues();
			values.put("settingName", settingName);
			values.put("settingValue", settingValue);
			
			if(c == null || c.getCount() == 0)
			{
				if(sqlDb.insert(ResearchDB.DATABASE_SETTINGS_TABLE_NAME, null, values) == -1)
				{
					throw new Exception("Couldn't insert the settings");
				}
			}
			
			if(sqlDb.update(ResearchDB.DATABASE_SETTINGS_TABLE_NAME, values, "settingName = ?", new String[]{settingName}) <= 0)
			{
				throw new Exception("Couldn't update the settings");
			}			
		}
		finally
		{
			if(c != null)
			{
				c.close();
			}
			if(sqlDb != null)
			{
				sqlDb.close();
			}
		}
	}
	
	public String getSetting(Context context, String settingName)
	{
		ResearchDB db = new ResearchDB(context);
		SQLiteDatabase sqlDb = null;
		String settingValue = null;
		Cursor c = null;
		
		try 
		{
			sqlDb = db.getReadableDatabase();
			c = sqlDb.rawQuery("SELECT * FROM " + ResearchDB.DATABASE_SETTINGS_TABLE_NAME + " s Where s.settingName = ?", new String[]{settingName});
			
			if(c != null && c.getCount() > 0)
			{
				c.moveToFirst();
				
				settingValue = c.getString(c.getColumnIndex("settingValue"));
			}			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			if(c != null)
			{
				c.close();
			}
			
			if(sqlDb != null)
			{
				sqlDb.close();
			}			
		}
		
		return settingValue;
	}
}
